<?php
/**
 * Shopware 4
 * Copyright © shopware AG
 *
 * According to our dual licensing model, this program can be used either
 * under the terms of the GNU Affero General Public License, version 3,
 * or under a proprietary license.
 *
 * The texts of the GNU Affero General Public License with an additional
 * permission and of our proprietary license can be found at and
 * in the LICENSE file you have received along with this program.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 *
 * "Shopware" is a registered trademark of shopware AG.
 * The licensing of the program under the AGPLv3 does not imply a
 * trademark license. Therefore any rights, title and interest in
 * our trademarks remain entirely with us.
 */

/**
 * Deprecated Shopware Class to provide article export feeds
 *
 * @category  Shopware
 * @package   Shopware\Core
 * @copyright Copyright (c) 2013, shopware AG (http://www.shopware.de)
 */
class sExport
{
    public $sFeedID;
    public $sHash;
    public $sSettings;
    public $sDB;
    public $sApi;
    public $sSYSTEM;
    public $sPath;
    public $sTemplates;

    public $sCurrency;
    public $sCustomergroup;
    public $sLanguage;
    public $sMultishop;

    public $shop;

    /**
     * @var Enlight_Template_Manager
     */
    public $sSmarty;

    /**
     * @var \Shopware\Models\Article\Repository
     */
    protected $articleRepository = null;

    /**
     * @var \Shopware\Models\Media\Repository
     */
    protected $mediaRepository = null;

    /**
     * @var \Shopware\Models\Media\Album
     */
    protected $articleMediaAlbum = null;

    public function sGetCurrency($currency)
    {
        static $cache = array();
        if(empty($currency))
            $currency = $this->sMultishop["defaultcurrency"];
        if(isset($cache[$currency]))
            return $cache[$currency];
        if(is_numeric($currency))
            $sql = "id=".$currency;
        elseif(is_string($currency))
            $sql = "currency=".$this->sDB->qstr(trim($currency));
        else
            return false;
        $sql = "
            SELECT *
            FROM s_core_currencies
            WHERE $sql
        ";
        return $cache[$currency] = $this->sDB->GetRow($sql);
    }

    public function sGetCustomergroup($customergroup)
    {
        static $cache = array();
        if(empty($customergroup))
            $customergroup = $this->sMultishop["defaultcustomergroup"];
        if(isset($cache[$customergroup]))
            return $cache[$customergroup];
        if(is_int($customergroup))
            $sql = "id=".$customergroup;
        elseif(is_string($customergroup))
            $sql = "groupkey=".$this->sDB->qstr(trim($customergroup));
        else
            return false;
        $sql = "
            SELECT *
            FROM s_core_customergroups
            WHERE $sql
        ";
        return $cache[$customergroup] = $this->sDB->GetRow($sql);
    }

    public function sGetMultishop($language)
    {
        static $cache = array();
        if(isset($cache[$language]))
            return $cache[$language];
        if(empty($language))
            $sql = "`default`=1";
        elseif(is_numeric($language))
            $sql = "id=".$language;
        elseif(is_string($language))
            $sql = "name=".$this->sDB->qstr(trim($language));

        $sql = "
            SELECT *
            FROM s_core_multilanguage
            WHERE $sql
        ";
        return $cache[$language] = $this->sDB->GetRow($sql);
    }

    public function sGetLanguage($language)
    {
        static $cache = array();
        if(isset($cache[$language]))
            return $cache[$language];
        if(empty($language))
            $sql = "default=1";
        elseif(is_numeric($language))
            $sql = "id=".$language;
        elseif(is_string($language))
            $sql = "isocode=".$this->sDB->qstr(trim($language));

        $sql = "
            SELECT *
            FROM s_core_multilanguage
            WHERE $sql
            ORDER BY skipbackend
        ";
        return $cache[$language] = $this->sDB->GetRow($sql);
    }

    /**
     * Helper function to get access to the article repository.
     * @return \Shopware\Models\Article\Repository
     */
    private function getArticleRepository()
    {
        if ($this->articleRepository === null) {
            $this->articleRepository = Shopware()->Models()->getRepository('Shopware\Models\Article\Article');
        }
        return $this->articleRepository;
    }

    /**
     * Helper function to get access to the media repository.
     * @return \Shopware\Models\Media\Repository
     */
    private function getMediaRepository()
    {
        if ($this->mediaRepository === null) {
            $this->mediaRepository = Shopware()->Models()->getRepository('Shopware\Models\Media\Media');
        }
        return $this->mediaRepository;
    }


    public function sInitSettings()
    {
        $hash = $this->sDB->qstr($this->sHash);

        /** @var $shopRepository \Shopware\Models\Shop\Repository */
        $shopRepository = Shopware()->Models()->getRepository('Shopware\Models\Shop\Shop');

        $sql = "
            SELECT
                id as feedID, s_export.*
            FROM
                s_export
            WHERE
                id = {$this->sFeedID}
            AND
                hash = $hash
            AND
                `active`=1
        ";
        $this->sSettings = $this->sDB->GetRow($sql);

        if(empty($this->sSettings))
            die();

        $this->sSettings["dec_separator"] = ",";
        switch ($this->sSettings["formatID"]) {
            case 1:
                $this->sSettings["fieldmark"] = "\"";
                $this->sSettings["escaped_fieldmark"] = "\"\"";
                $this->sSettings["separator"] = ";";
                $this->sSettings["escaped_separator"] = ";";
                $this->sSettings["line_separator"] = "\r\n";
                $this->sSettings["escaped_line_separator"] = "\r\n";
                break;
            case 2:
                $this->sSettings["fieldmark"] = "";
                $this->sSettings["escaped_fieldmark"] = "";
                $this->sSettings["separator"] = "\t";
                $this->sSettings["escaped_separator"] = "";
                $this->sSettings["line_separator"] = "\r\n";
                $this->sSettings["escaped_line_separator"] = "";
                break;
            case 4:
                $this->sSettings["fieldmark"] = "";
                $this->sSettings["escaped_fieldmark"] = "";
                $this->sSettings["separator"] = "|";
                $this->sSettings["escaped_separator"] = "";
                $this->sSettings["line_separator"] = "\r\n";
                $this->sSettings["escaped_line_separator"] = "";
                break;
            default:
                $this->sSettings["fieldmark"] = null;
                $this->sSettings["escaped_fieldmark"] = null;
                $this->sSettings["separator"] = null;
                $this->sSettings["escaped_separator"] = null;
                $this->sSettings["line_separator"] = null;
                $this->sSettings["escaped_line_separator"] = null;
        }

        if (!empty($this->sSettings['encodingID']) && $this->sSettings['encodingID']==2) {
            $this->sSettings['encoding'] = 'UTF-8';
        } else {
            $this->sSettings['encoding'] = 'ISO-8859-1';
        }

        if (empty($this->sSettings["languageID"])) {
            $defaultShop = $shopRepository->getDefault();
            //just a fall back for update reasons
            $this->sSettings["languageID"] = $defaultShop->getId();
        }

        $this->sMultishop = $this->sGetMultishop($this->sSettings["languageID"]);

        if (empty($this->sSettings["categoryID"])) {
            $this->sSettings["categoryID"] = $this->sMultishop["parentID"];
        }
        if (empty($this->sSettings["customergroupID"])) {
            $this->sSettings["customergroupID"] = $this->sMultishop["defaultcustomergroup"];
        } else {
            $this->sSettings["customergroupID"] = (int) $this->sSettings["customergroupID"];
        }
        if (empty($this->sSettings["currencyID"])) {
            $this->sSettings["currencyID"] = $this->sMultishop["defaultcurrency"];
        }

        $this->sLanguage = $this->sGetMultishop($this->sSettings["languageID"]);

        $this->sCurrency = $this->sGetCurrency($this->sSettings["currencyID"]);

        $this->sCustomergroup = $this->sGetCustomergroup($this->sSettings["customergroupID"]);


        $this->articleMediaAlbum = $this->getMediaRepository()
                ->getAlbumWithSettingsQuery(-1)
                ->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_OBJECT);

        $shop = $shopRepository->getActiveById($this->sLanguage['id']);

        $repository = Shopware()->Models()->getRepository('Shopware\Models\Shop\Currency');
        $shop->setCurrency($repository->find($this->sCurrency['id']));
        $shop->registerResources(Shopware()->Bootstrap());

        $this->shop = $shop;

        $this->sSYSTEM->sCONFIG = Shopware()->Config();
    }

    public function sInitSmarty()
    {
        $this->sSYSTEM->sSMARTY->compile_id = "export_".$this->sFeedID;

        $this->sSYSTEM->sSMARTY->cache_lifetime = 0;
        $this->sSYSTEM->sSMARTY->debugging = 0;
        $this->sSYSTEM->sSMARTY->caching = 0;

        $this->sSmarty->registerPlugin('modifier', 'format', array(&$this,'sFormatString'));
        $this->sSmarty->registerPlugin('modifier', 'escape', array(&$this,'sEscapeString'));
        $this->sSmarty->registerPlugin('modifier', 'category', array(&$this,'sGetArticleCategoryPath'));
        $this->sSmarty->registerPlugin('modifier', 'link', array(&$this,'sGetArticleLink'));
        $this->sSmarty->registerPlugin('modifier', 'image', array(&$this,'sGetImageLink'));
        $this->sSmarty->registerPlugin('modifier', 'articleImages', array(&$this,'sGetArticleImageLinks'));
        $this->sSmarty->registerPlugin('modifier', 'shippingcost', array(&$this,'sGetArticleShippingcost'));
        $this->sSmarty->registerPlugin('modifier', 'property', array(&$this,'sGetArticleProperties'));

        $this->sSmarty->assign("sConfig",$this->sSYSTEM->sCONFIG);
        $this->sSmarty->assign("sLanguage",$this->sLanguage);
        $this->sSmarty->assign("sMultishop",$this->sMultishop);
        $this->sSmarty->assign("sCurrency",$this->sCurrency);
        $this->sSmarty->assign("sCustomergroup",$this->sCustomergroup);
        $this->sSmarty->assign("sSettings",$this->sSettings);

        $this->sSmarty->config_vars["F"] = $this->sSettings["fieldmark"];
        $this->sSmarty->config_vars["EF"] = $this->sSettings["escaped_separator"];
        $this->sSmarty->config_vars["S"] = $this->sSettings["separator"];
        $this->sSmarty->config_vars["ES"] = $this->sSettings["escaped_fieldmark"];
        $this->sSmarty->config_vars["L"] = $this->sSettings["line_separator"];
        $this->sSmarty->config_vars["EL"] = $this->sSettings["escaped_line_separator"];
        if ($this->sSettings['encoding'] == 'UTF-8') {
            $this->sSmarty->config_vars['BOM'] = "\xEF\xBB\xBF";
        } else {
            $this->sSmarty->config_vars['BOM'] = '';
        }
        $this->sSmarty->config_vars['EN'] = $this->sSettings['encoding'];
    }

    public function sFormatString($string, $esc_type = '', $char_set = null)
    {
        return $this->sEscapeString($string, $esc_type, $char_set);
    }

    public function sEscapeString($string, $esc_type = '', $char_set = null)
    {
        if (empty($esc_type)) {
            if (!empty($this->sSettings["formatID"]) && $this->sSettings["formatID"]==3) {
                $esc_type = "html";
            } else {
                $esc_type = "csv";
            }
        }

        if (empty($char_set)) {
            $char_set = $this->sSettings['encoding'];
        }

        switch ($esc_type) {
            case 'number':
                return number_format($string,2,$this->sSettings["dec_separator"],'');
            case 'csv':
                if (empty($this->sSettings["escaped_line_separator"])) {
                    $string = preg_replace('#[\r\n]+#m', ' ', $string);
                } elseif ($this->sSettings["escaped_line_separator"]!=$this->sSettings["line_separator"]) {
                    $string = str_replace($this->sSettings["line_separator"],$this->sSettings['escaped_line_separator'],$string);
                }
                if (!empty($this->sSettings["fieldmark"])) {
                    $string = str_replace($this->sSettings["fieldmark"],$this->sSettings['escaped_fieldmark'],$string);
                } else {
                    $string = str_replace($this->sSettings['separator'],$this->sSettings['escaped_separator'],$string);
                }

                if ($char_set != 'UTF-8') {
                    $string = utf8_decode($string);
                }
                $string = html_entity_decode($string, ENT_NOQUOTES, $char_set);
                return $this->sSettings["fieldmark"].$string.$this->sSettings["fieldmark"];
            case 'xml':
                 if ($char_set != 'UTF-8') {
                     $string = utf8_decode($string);
                 }
                return $string;
               case 'html':
                $string = html_entity_decode($string, ENT_NOQUOTES, $char_set);
                   return htmlspecialchars($string, ENT_QUOTES, $char_set, false);
            case 'htmlall':
                return htmlentities($string, ENT_QUOTES, $char_set);
            case 'url':
                return rawurlencode($string);
            case 'urlpathinfo':
                return str_replace('%2F','/',rawurlencode($string));

            case 'quotes':
                // escape unescaped single quotes
                return preg_replace("%(?<!\\\\)'%", "\\'", $string);

            case 'hex':
                // escape every character into hex
                $return = '';
                for ($x=0; $x < strlen($string); $x++) {
                    $return .= '%' . bin2hex($string[$x]);
                }
                return $return;

            case 'hexentity':
                $return = '';
                for ($x=0; $x < strlen($string); $x++) {
                    $return .= '&#x' . bin2hex($string[$x]) . ';';
                }
                return $return;

            case 'decentity':
                $return = '';
                for ($x=0; $x < strlen($string); $x++) {
                    $return .= '&#' . ord($string[$x]) . ';';
                }
                return $return;

            case 'javascript':
                // escape quotes and backslashes, newlines, etc.
                return strtr($string, array('\\'=>'\\\\',"'"=>"\\'",'"'=>'\\"',"\r"=>'\\r',"\n"=>'\\n','</'=>'<\/'));

            case 'mail':
                // safe way to display e-mail address on a web page
                return str_replace(array('@', '.'),array(' [AT] ', ' [DOT] '), $string);

            case 'nonstd':
               // escape non-standard chars, such as ms document quotes
               $_res = '';
               for ($_i = 0, $_len = strlen($string); $_i < $_len; $_i++) {
                   $_ord = ord(substr($string, $_i, 1));
                   // non-standard char, escape it
                   if ($_ord >= 126) {
                       $_res .= '&#' . $_ord . ';';
                   } else {
                       $_res .= substr($string, $_i, 1);
                   }
               }
               return $_res;
        }
    }

    public function sGetArticleLink($articleID, $title="")
    {
        return Shopware()->Modules()->Core()->sRewriteLink($this->sSYSTEM->sCONFIG["sBASEFILE"]."?sViewport=detail&sArticle=$articleID",$title).(empty($this->sSettings["partnerID"])?"":"?sPartner=".urlencode($this->sSettings["partnerID"]));
    }

    public function sGetImageLink($hash, $imageSize = null)
    {
        if (empty($hash)) {
            return "";
        }

        // get the image directory
        $imageDir = 'http://' . $this->shop->getHost() . $this->shop->getBasePath() . '/media/image/';

        // if no imageSize was set, return the full image
        if (null === $imageSize) {
            return $imageDir . $hash;
        }

        // get filename and extension in order to insert thumbnail size later
        $extension = pathinfo($hash, PATHINFO_EXTENSION);
        $fileName = pathinfo($hash, PATHINFO_FILENAME);
        $thumbDir = $imageDir . 'thumbnail/';

        // get thumbnail sizes
        $sizes = $this->articleMediaAlbum
                ->getSettings()
                ->getThumbnailSize();

        foreach ($sizes as $key => &$size) {
            if (strpos($size, 'x') === 0) {
                $size = $size . 'x' . $size;
            }
        }

        if (isset($sizes[$imageSize])) {
            return $thumbDir . $fileName . '_' . $sizes[(int) $imageSize] . '.' . $extension;
        }

        return "";
    }

    /**
     * Returns the article image links with the frontend logic.
     * Checks the image restriction of variant articles, too.
     *
     * @param $articleId
     * @param $orderNumber
     * @param null $imageSize
     * @param string $separator
     * @return string
     */
    public function sGetArticleImageLinks($articleId, $orderNumber, $imageSize = null, $separator = "|" )
    {
        $imageSize = ($imageSize == null) ? "original" : $imageSize;
        $returnData = array();
        if (empty($articleId) || empty($orderNumber)) {
            return "";
        }
        $imageData = Shopware()->Modules()->sArticles()->sGetArticlePictures($articleId, false, null, $orderNumber);
        $cover = Shopware()->Modules()->sArticles()->sGetArticlePictures($articleId, true, null, $orderNumber);
        $returnData[] = $cover["src"][$imageSize];
        foreach ($imageData as $image) {
            $returnData[] = $image["src"][$imageSize];
        }

        return implode($separator, $returnData);
    }

    /**
     * Returns an array with the article property data.
     * Needs to be parsed over the feed smarty template
     *
     * @param $articleId
     * @param $filterGroupId
     * @return string
     */
    public function sGetArticleProperties($articleId, $filterGroupId  )
    {
        if (empty($articleId) || empty($filterGroupId)) {
            return "";
        }
        return Shopware()->Modules()->Articles()->sGetArticleProperties($articleId, $filterGroupId);
    }

    public function sMapTranslation($object,$objectdata)
    {

        switch ($object) {
            case "detail":
            case "article":
                $map = array("txtshortdescription"=>"description","txtlangbeschreibung"=>"description_long","txtArtikel"=>"name","txtzusatztxt"=>"additionaltext");
                for ($i=1;$i<=20;$i++)
                    $map["attr$i"] = "attr$i";
                break;
            case "link":
                $map = array("linkname"=>"description");
                break;
            case "download":
                $map = array("downloadname"=>"description");
                break;
        }
        if (empty($objectdata))
            return array();
        $objectdata = unserialize($objectdata);
        if (empty($objectdata))
            return array();
        $result = array();
        foreach ($map as $key=>$value) {
            if(isset($objectdata[$key]))
                $result[$value] = $objectdata[$key];
        }
        return $result;
    }

    public function _decode_line($line)
    {
        $separator = ";";
        $fieldmark = "\"";
        $elements = explode($separator, $line);
        $tmp_elements = array();
        for ($i = 0; $i < count($elements); $i++) {
            $nquotes = substr_count($elements[$i], $fieldmark);
            if ($nquotes %2 == 1) {
                if(isset($elements[$i+1]))
                       $elements[$i+1] = $elements[$i].$separator.$elements[$i+1];
            } else {
                if ($nquotes > 0) {
                    if(substr($elements[$i],0,1)==$fieldmark)
                        $elements[$i] = substr($elements[$i],1);
                    if(substr($elements[$i],-1,1)==$fieldmark)
                        $elements[$i] = substr($elements[$i],0,-1);
                    $elements[$i] = str_replace($fieldmark.$fieldmark, $fieldmark, $elements[$i]);
                }
                $tmp_elements[] = $elements[$i];
            }
        }
        return $tmp_elements;
    }

    public function sCreateSql()
    {
        $sql_add_join   = array();
        $sql_add_select = array();
        $sql_add_where  = array();

        if (empty($this->sLanguage["skipbackend"]) && !empty($this->sLanguage["isocode"])) {
            $sql_isocode = $this->sDB->qstr($this->sLanguage["isocode"]);
            $sql_add_join[] = "
                LEFT JOIN s_core_translations as ta
                ON ta.objectkey=a.id AND ta.objecttype='article' AND ta.objectlanguage=$sql_isocode

                LEFT JOIN s_core_translations as td
                ON td.objectkey=d.id AND td.objecttype='variant' AND td.objectlanguage=$sql_isocode
            ";
            $sql_add_select[] = "ta.objectdata as article_translation";
            $sql_add_select[] = "td.objectdata as detail_translation";

            //read the fallback for the case the translation is not going to be set
            if (!empty($this->sLanguage["fallback"])) {
                $sqlFallbackLanguageId = $this->sDB->qstr($this->sLanguage["fallback"]);
                $sql_add_join[] = "
                LEFT JOIN s_core_translations as taf
                ON taf.objectkey=a.id AND taf.objecttype='article' AND taf.objectlanguage=$sqlFallbackLanguageId

                LEFT JOIN s_core_translations as tdf
                ON tdf.objectkey=d.id AND tdf.objecttype='variant' AND tdf.objectlanguage=$sqlFallbackLanguageId
            ";
                $sql_add_select[] = "taf.objectdata as article_translation_fallback";
                $sql_add_select[] = "tdf.objectdata as detail_translation_fallback";
            }

        }
        if (!empty($this->sSettings["categoryID"])) {
            $sql_add_join[] = "
                INNER JOIN s_articles_categories_ro act
                    ON  act.articleID = a.id
                    AND act.categoryID = {$this->sSettings["categoryID"]}
                INNER JOIN s_categories c
                    ON  c.id = act.categoryID
                    AND c.active = 1
            ";
        }
        if (empty($this->sSettings["image_filter"])) {
            $sql_add_join[] = "
                LEFT JOIN s_articles_img as i
                ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL
            ";
        } else {
            $sql_add_join[] = "
                JOIN s_articles_img as i
                ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL
            ";
        }

        if (!empty($this->sCustomergroup["groupkey"])&&empty($this->sCustomergroup["mode"])&&$this->sCustomergroup["groupkey"]!="EK") {
            $sql_add_join[] = "
                LEFT JOIN s_articles_prices as p2
                ON p2.articledetailsID = d.id AND p2.`from`=1
                AND p2.pricegroup='{$this->sCustomergroup["groupkey"]}'
                AND p2.price!=0
            ";
            $pricefield = "IFNULL(p2.price, p.price)";
            $pseudoprice = "IFNULL(p2.pseudoprice, p.pseudoprice)";
            $baseprice = "IFNULL(p2.baseprice, p.baseprice)";
        } else {
            $pricefield = "p.price";
            $pseudoprice = "p.pseudoprice";
            $baseprice = "p.baseprice";
        }


        if (empty($this->sSettings["variant_export"])||$this->sSettings["variant_export"]==1) {

            $sql_add_select[] = "IF(COUNT(d.ordernumber)<=1,'',GROUP_CONCAT(CONCAT('\"',REPLACE(d.ordernumber,'\"','\"\"'),'\"') SEPARATOR ';')) as group_ordernumber";
            $sql_add_select[] = "IF(COUNT(d.additionaltext)<=1,'',GROUP_CONCAT(CONCAT('\"',REPLACE(d.additionaltext,'\"','\"\"'),'\"') SEPARATOR ';')) as group_additionaltext";
            $sql_add_select[] = "IF(COUNT($pricefield)<=1,'',GROUP_CONCAT(ROUND(CAST($pricefield*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) SEPARATOR ';')) as group_pricenet";
            $sql_add_select[] = "IF(COUNT($pricefield)<=1,'',GROUP_CONCAT(ROUND(CAST($pricefield*(100+t.tax-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) SEPARATOR ';')) as group_price";
            $sql_add_select[] = "IF(COUNT(d.active)<=1,'',GROUP_CONCAT(d.active SEPARATOR ';')) as group_active";
            $sql_add_select[] = "IF(COUNT(d.instock)<=1,'',GROUP_CONCAT(d.instock SEPARATOR ';')) as group_instock";

        }

        $grouppricefield = "gp.price";
        if (empty($this->sSettings["variant_export"])||$this->sSettings["variant_export"]==2||$this->sSettings["variant_export"]==1) {
            $sql_add_join[] = "
                JOIN (SELECT NULL as `articleID` , NULL as `valueID` , NULL as `attr1` , NULL as `attr2` , NULL as `attr3` , NULL as `attr4` , NULL as `attr5` , NULL as `attr6` , NULL as `attr7` , NULL as `attr8` , NULL as `attr9` , NULL as `attr10` , NULL as `standard` , NULL as `active` , NULL as `ordernumber` , NULL as `instock`) as v
            ";

            $sql_add_join[] = "
                JOIN (SELECT NULL as articleID, NULL as valueID, NULL as groupkey, NULL as price, NULL as optionID) as gp
            ";
        }

        $sql_add_article_detail_join_condition ='';
        if (empty($this->sSettings["variant_export"])||$this->sSettings["variant_export"]==1) {
            $sql_add_group_by = "a.id";
            $configurator_settings_sql = ", NULL as configurator_settings";
            $sql_add_article_detail_join_condition = "AND d.kind=1";
        } elseif ($this->sSettings["variant_export"]==2) {
            $sql_add_group_by = "d.id";
            // If an article is a configurator article, show which configurator groups and options are set
            $configurator_settings_sql = "
            , (
                SELECT
                    GROUP_CONCAT(CONCAT(acg.name, ':', aco.name) SEPARATOR ', ')
                FROM
                    s_article_configurator_option_relations cor
                LEFT JOIN
                    s_article_configurator_options aco ON aco.id=cor.option_id
                LEFT JOIN
                    s_article_configurator_groups acg ON acg.id=aco.group_id
                WHERE
                    cor.article_id=d.id
            ) as configurator_settings";
        } else {
            //$sql_add_group_by = "d.id, v.valueID";
        }

        if (!empty($this->sSettings["active_filter"])) {
            $sql_add_where[] = "(v.active=1 OR (v.active IS NULL AND a.active=1))";
        }
        if (!empty($this->sSettings["stockmin_filter"])) {
            $sql_add_where[] ="(v.instock>=d.stockmin OR (v.instock IS NULL AND d.instock>=d.stockmin))";
        }
        if (!empty($this->sSettings["instock_filter"])) {
            $sql_add_where[] ="(v.instock>={$this->sSettings["instock_filter"]} OR (v.instock IS NULL AND d.instock>={$this->sSettings["instock_filter"]}))";
        }
        if (!empty($this->sSettings["price_filter"])) {
            $sql_add_where[] = "ROUND(CAST(IFNULL($grouppricefield,$pricefield)*(100+t.tax-IF(pd.discount IS NULL,0,pd.discount)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2)>=".$this->sSettings["price_filter"];
        }
        if (!empty($this->sSettings["own_filter"])&&trim($this->sSettings["own_filter"])) {
            $sql_add_where[] = "(".$this->sSettings["own_filter"].")";
        }

        $sql_add_join = implode(" ",$sql_add_join);
        if(!empty($sql_add_select))
            $sql_add_select = ", ".implode(", ",$sql_add_select);
        else
            $sql_add_select = "";
        if(!empty($sql_add_where))
            $sql_add_where = " AND ".implode(" AND ",$sql_add_where);
        else
            $sql_add_where = "";
        if(!empty($sql_add_group_by))
            $sql_add_group_by = "GROUP BY ($sql_add_group_by)";
        else
            $sql_add_group_by = "";

        $sql = "
            SELECT
                a.id as `articleID`,
                a.name,
                a.description,
                a.description_long,
                d.shippingtime,
                d.shippingfree,
                a.topseller,
                a.keywords,
                d.minpurchase,
                d.purchasesteps,
                d.maxpurchase,
                d.purchaseunit,
                d.referenceunit,
                a.taxID,
                a.filtergroupID,
                a.supplierID,
                d.unitID,
                IF(a.changetime!='0000-00-00 00:00:00',a.changetime,'') as `changed`,
                IF(a.datum!='0000-00-00',a.datum,'') as `added`,
                IF(d.releasedate!='0000-00-00',d.releasedate,'') as `releasedate`,
                a.active as active,

                d.id as `articledetailsID`,
                IF(v.ordernumber IS NOT NULL,v.ordernumber,d.ordernumber) as ordernumber,

                d.suppliernumber,
                d.ean,
                d.width,
                d.height,
                d.length,
                d.kind,
                IF(v.standard=1||kind=1,1,0) as standard,
                d.additionaltext,
                d.impressions,
                d.sales,


                IF(v.active IS NOT NULL,IF(a.active=0,0,v.active),a.active) as active,
                IF(v.instock IS NOT NULL,v.instock,d.instock) as instock,
                (
                   SELECT AVG(av.points)
                   FROM s_articles_vote as av WHERE active=1
                   AND articleID=a.id
                ) as sVoteAverage,
                (
                   SELECT COUNT(*)
                   FROM s_articles_vote as av WHERE active=1
                   AND articleID=a.id
                ) as sVoteCount,
                d.stockmin,
                d.weight,
                d.position,

                at.attr1, at.attr2, at.attr3, at.attr4, at.attr5, at.attr6, at.attr7, at.attr8, at.attr9, at.attr10,
                at.attr11, at.attr12, at.attr13, at.attr14, at.attr15, at.attr16, at.attr17, at.attr18, at.attr19, at.attr20,

                s.name as supplier,
                u.unit,
                u.description as unit_description,
                t.tax,
                CONCAT(i.img, '.', i.extension) as image,

                a.configurator_set_id as configurator,

                ROUND(CAST(IFNULL($grouppricefield, $pricefield)*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as netprice,
                ROUND(CAST(IFNULL($grouppricefield, $pricefield)*(100+t.tax)/100*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as price,
                pd.discount,
                ROUND(CAST($pseudoprice*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as netpseudoprice,
                ROUND(CAST($pseudoprice*(100+t.tax)*{$this->sCurrency["factor"]}/100 AS DECIMAL(10,3)),2) as pseudoprice,
                $baseprice,
                IF(file IS NULL,0,1) as esd

                $configurator_settings_sql

                $sql_add_select

            FROM s_articles a
            INNER JOIN s_articles_details d
            ON d.articleID = a.id
            $sql_add_article_detail_join_condition
            LEFT JOIN s_articles_attributes at
            ON d.id = at.articledetailsID

            LEFT JOIN `s_core_units` as `u`
            ON d.unitID = u.id
            LEFT JOIN `s_core_tax` as `t`
            ON a.taxID = t.id
            LEFT JOIN `s_articles_supplier` as `s`
            ON a.supplierID = s.id

            LEFT JOIN s_core_pricegroups_discounts pd
            ON a.pricegroupActive=1
            AND	a.pricegroupID=groupID
            AND customergroupID = 1
            AND discountstart=1

            LEFT JOIN s_articles_esd e ON e.articledetailsID=d.id

            LEFT JOIN (
                SELECT articleID
                FROM
                    s_export_categories as ec,
                    s_articles_categories_ro as ac
                WHERE feedID={$this->sFeedID}
                AND ec.categoryID=ac.categoryID
                GROUP BY articleID
            ) AS bc
            ON bc.articleID=a.id

            LEFT JOIN s_export_suppliers AS bs
            ON (bs.supplierID=s.id AND bs.feedID={$this->sFeedID})

            LEFT JOIN s_export_articles AS ba
            ON (ba.articleID=a.id AND ba.feedID={$this->sFeedID})

            LEFT JOIN s_articles_prices as p
            ON p.articledetailsID = d.id
            AND p.`from`=1
            AND p.pricegroup='EK'

            $sql_add_join

            WHERE bc.articleID IS NULL
            AND bs.supplierID IS NULL
            AND a.mode = 0
            AND d.kind != 3
            AND ba.articleID IS NULL
            $sql_add_where

            $sql_add_group_by
        ";

        if (!empty($this->sSettings["count_filter"])) {
            $sql .= "LIMIT ".$this->sSettings["count_filter"];
        }
        return  $sql;
    }

    /**
     * executes the current product export
     *
     * @param resource $handleResource used as a file or the stdout to fetch the smarty output
     */
    public function executeExport($handleResource)
    {
        fwrite($handleResource, $this->sSmarty->fetch('string:' . $this->sSettings['header'], $this->sFeedID));

        $sql = $this->sCreateSql();

        $result = Shopware()->Db()->query($sql);

        if ($result === false) {
            return;
        }

        // Update db with the latest values
        $count = (int) $result->rowCount();
        Shopware()->Db()->update(
            's_export',
            array(
                'last_export' => new Zend_Date(),
                'cache_refreshed' => new Zend_Date(),
                'count_articles' => $count
            ),
            array('id = ?' => $this->sFeedID)
        );

        // fetches all required data to smarty
        $rows = array();
        for ($rowIndex = 1; $row = $result->fetch(); $rowIndex++) {

            if (!empty($row['group_ordernumber_2'])) {
                $row['group_ordernumber'] = $this->_decode_line($row['group_ordernumber_2']);
                $row['group_pricenet'] = explode(';', $row['group_pricenet_2']);
                $row['group_price'] = explode(';', $row['group_price_2']);
                $row['group_instock'] = explode(';', $row['group_instock_2']);
                $row['group_active'] = explode(';', $row['group_active_2']);
                unset($row['group_ordernumber_2'], $row['group_pricenet_2']);
                unset($row['group_price_2'], $row['group_instock_2'], $row['group_active_2']);
                for ($i = 1; $i <= 10; $i++) {
                    if (!empty($row['group_group' . $i])) {
                        $row['group_group' . $i] = $this->_decode_line($row['group_group' . $i]);
                    } else {
                        unset($row['group_group' . $i]);
                    }
                    if (!empty($row['group_option' . $i])) {
                        $row['group_option' . $i] = $this->_decode_line($row['group_option' . $i]);
                    } else {
                        unset($row['group_option' . $i]);
                    }
                }
                unset($row['group_additionaltext']);
            } elseif (!empty($row['group_ordernumber'])) {
                $row['group_ordernumber'] = $this->_decode_line($row['group_ordernumber']);
                $row['group_additionaltext'] = $this->_decode_line($row['group_additionaltext']);
                $row['group_pricenet'] = explode(';', $row['group_pricenet']);
                $row['group_price'] = explode(';', $row['group_price']);
                $row['group_instock'] = explode(';', $row['group_instock']);
                $row['group_active'] = explode(';', $row['group_active']);
            }
            if (!empty($row['article_translation'])) {
                $translation = $this->sMapTranslation('article', $row['article_translation']);
                $row = array_merge($row, $translation);
            } elseif (!empty($row['article_translation_fallback'])) {
                $translation = $this->sMapTranslation('article', $row['article_translation_fallback']);
                $row = array_merge($row, $translation);
            }
            if (!empty($row['detail_translation'])) {
                $translation = $this->sMapTranslation('detail', $row['detail_translation']);
                $row = array_merge($row, $translation);
            } elseif (!empty($row['detail_translation_fallback'])) {
                $translation = $this->sMapTranslation('detail', $row['detail_translation_fallback']);
                $row = array_merge($row, $translation);
            }
            $row['name'] = htmlspecialchars_decode($row['name']);
            $row['supplier'] = htmlspecialchars_decode($row['supplier']);

            //cast it to float to prevent the devision by zero warning
            $row['purchaseunit'] = floatval($row['purchaseunit']);
            $row['referenceunit'] = floatval($row['referenceunit']);
            if (!empty($row['purchaseunit']) && !empty($row['referenceunit'])) {
                $row['referenceprice'] = Shopware()->Modules()->Articles()->calculateReferencePrice(
                    $row['price'],
                    $row['purchaseunit'],
                    $row['referenceunit']
                );
            }

            $rows[] = $row;

            if ($rowIndex == $count || count($rows) >= 50) {

                @set_time_limit(30);

                $this->sSmarty->assign('sArticles', $rows);
                $rows = array();

                $template = 'string:{foreach $sArticles as $sArticle}' . $this->sSettings['body'] . '{/foreach}';

                fwrite($handleResource, $this->sSmarty->fetch($template, $this->sFeedID));
            }
        }
        fwrite($handleResource, $this->sSmarty->fetch('string:' . $this->sSettings['footer'], $this->sFeedID));
        fclose($handleResource);
    }

    public function sGetArticleCategoryPath($articleID, $separator = " > ", $categoryID=null)
    {
        if(empty($categoryID))
            $categoryID = $this->sSettings["categoryID"];

        $articleCategoryId = $this->sSYSTEM->sMODULES["sCategories"]->sGetCategoryIdByArticleId($articleID,$categoryID);
        $breadcrumb = array_reverse(Shopware()->Modules()->sCategories()->sGetCategoriesByParent($articleCategoryId));

        foreach ($breadcrumb as $breadcrumbObj) {
            $breadcrumbs[] = $breadcrumbObj["name"];
        }
        return htmlspecialchars_decode(implode($separator,$breadcrumbs));
    }

    public function sGetCountry($country)
    {
        static $cache = array();
        if(empty($country))
            return false;
        if(isset($cache[$country]))
            return $cache[$country];
        if(is_numeric($country))
            $sql = "c.id=".$country;
        elseif(is_string($country))
            $sql = "c.countryiso=".$this->sDB->qstr($country);
        else
            return false;
        $sql = "
            SELECT
              c.id, c.id as countryID, countryname, countryiso,
              countryen, c.position, notice,
              c.shippingfree as shippingfree
            FROM s_core_countries c
            WHERE $sql
        ";
        return $cache[$country] = $this->sDB->GetRow($sql);
    }

    public function sGetPaymentmean($payment)
    {
        static $cache = array();
        if(empty($payment))
            return false;
        if(isset($cache[$payment]))
            return $cache[$payment];
        if(is_numeric($payment))
            $sql = "id=".$payment;
        elseif(is_string($payment))
            $sql = "name=".$this->sDB->qstr($payment);
        else
            return false;
        $sql = "
            SELECT * FROM s_core_paymentmeans
            WHERE $sql
        ";
        $cache[$payment] = $this->sDB->GetRow($sql);

        $cache[$payment]["country_surcharge"] = array();
        if (!empty($cache[$payment]["surchargestring"])) {
            foreach (explode(";",$cache[$payment]["surchargestring"]) as $countrySurcharge) {
                list($key,$value) = explode(":",$countrySurcharge);
                $value = floatval(str_replace(",",".",$value));
                if (!empty($value)) {
                    $cache[$payment]["country_surcharge"][$key] = $value;
                }
            }
        }
        $cache[$payment]["surcharge"] = $cache[$payment]["surcharge"];
        return $cache[$payment];
    }

    public function sGetDispatch($dispatch = null, $country = null)
    {
        if(empty($dispatch))
            $sql_order = "";
        elseif(is_numeric($dispatch))
            $sql_order = "IF(sd.id=".(int) $dispatch.",0,1),";
        elseif(is_string($dispatch))
            $sql_order = "IF(name=".$this->sDB->qstr($dispatch).",0,1),";
        else
            $sql_order = "";

        if(empty($country))
            $sql_where = "";
        elseif(is_numeric($country))
            $sql_where = "c.id=".$country;
        elseif(is_string($country))
            $sql_where = "c.countryiso=".$this->sDB->qstr($country);
        else
            $sql_where = "";

        static $cache = array();
        if(isset($cache[$sql_order."|".$sql_where]))
            return $cache[$sql_order."|".$sql_where];

        if (!empty($sql_where)) {
            $sql_from = " s_premium_dispatch_countries sc,	s_core_countries c";
            $sql_where = "AND $sql_where AND c.id=sc.countryID";
        } else {
            $sql_from = "";
        }
        $sql = "
            SELECT sd.id, name, sd.description, sd.shippingfree
            FROM
                s_premium_dispatch sd,
                $sql_from
            WHERE sd.active = 1
            AND	sd.id = sc.dispatchID
            $sql_where
            ORDER BY $sql_order sd.position ASC LIMIT 1
        ";
        return $cache[$sql_order."|".$sql_where] = $this->sDB->GetRow($sql);
    }

    public function sGetDispatchBasket($article, $countryID=null, $paymentID = null)
    {
        $sql_select = '';
        if (!empty($this->sSYSTEM->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT'])) {
            $sql_select .= ', '.$this->sSYSTEM->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT'];
        }
        $sql = 'SELECT id, calculation_sql FROM s_premium_dispatch WHERE calculation=3';
        $calculations = $this->sDB->GetAssoc($sql);
        if(!empty($calculations))
        foreach ($calculations as $dispatchID => $calculation) {
            if(empty($calculation)) $calculation = $this->sSYSTEM->sDB_CONNECTION->qstr($calculation);
            $sql_select .= ', ('.$calculation.') as calculation_value_'.$dispatchID;
        }

        $sql = "
            SELECT
                MIN(d.instock>=b.quantity) as instock,
                MIN(d.instock>=(b.quantity+d.stockmin)) as stockmin,
                MIN(a.laststock) as laststock,
                SUM(d.weight*b.quantity) as weight,
                SUM(IF(a.id,b.quantity,0)) as count_article,
                MAX(b.shippingfree) as shippingfree,
                SUM(IF(b.modus=0,b.quantity*b.price/b.currencyFactor,0)) as amount,
                MAX(t.tax) as max_tax, u.id as userID
                $sql_select
                , b.articleID
            FROM (
                SELECT
                    NULL as sessionID,
                    ? as articleID,
                    ? as ordernumber,
                    ? as shippingfree,
                    1 as quantity,
                    ? as price,
                    ? as netprice,
                    0 as modus,
                    ? as esdarticle,
                    '' as config,
                    ? as currencyFactor,
                    '' as ob_attr1,
                    '' as ob_attr2,
                    '' as ob_attr3,
                    '' as ob_attr4,
                    '' as ob_attr5,
                    '' as ob_attr6
            ) as b

            LEFT JOIN s_articles a
            ON b.articleID=a.id
            AND b.modus=0
            AND b.esdarticle=0

            LEFT JOIN s_articles_details d
            ON (d.ordernumber=b.ordernumber)
            AND d.articleID=a.id

            LEFT JOIN s_articles_attributes at
            ON at.articledetailsID=d.id

            LEFT JOIN s_core_tax t
            ON t.id=a.taxID

            LEFT JOIN s_user u
            ON u.id=NULL

            LEFT JOIN s_user_billingaddress ub
            ON ub.userID=u.id

            LEFT JOIN s_user_shippingaddress us
            ON us.userID=u.id

            GROUP BY b.sessionID
        ";

        try {
        $basket = $this->sDB->GetRow($sql,array(
            $article["articleID"],
            $article["ordernumber"],
            $article["shippingfree"],
            $article["price"],
            $article["netprice"],
            $article["esd"],
            $this->sCurrency["factor"]
        ));
        } catch (Exception $e) {
            echo $e->getMessage();
            exit();
        }

        if (empty($basket)) {
            return false;
        }
        $basket['countryID'] = $countryID;
        $basket['paymentID'] = $paymentID;
        $basket['customergroupID'] = $this->sCustomergroup['id'];
        $basket['multishopID'] = $this->sMultishop['id'];
        $basket['sessionID'] = null;
        return $basket;
    }

    public function sGetArticleShippingcost($article, $payment, $country, $dispatch = null)
    {
        if(empty($article)||!is_array($article)) return false;
        $country = $this->sGetCountry($country);
        if(empty($country)) return false;
        $payment = $this->sGetPaymentmean($payment);
        if(empty($payment)) return false;
        if (!empty($payment["country_surcharge"][$country["countryiso"]]))
            $payment["surcharge"] += $payment["country_surcharge"][$country["countryiso"]];
        $payment['surcharge'] = round($payment['surcharge']*$this->sCurrency["factor"],2);

        return $this->sGetArticlePremiumShippingcosts($article,$payment,$country,$dispatch);
    }

    public function sGetPremiumDispatch($basket, $dispatch = null)
    {
        if(empty($dispatch))
            $sql_order = "";
        elseif(is_numeric($dispatch))
            $sql_order = "IF(d.id=".(int) $dispatch.",0,1),";
        elseif(is_string($dispatch))
            $sql_order = "IF(d.name=".$this->sDB->qstr($dispatch).",0,1),";
        else
            $sql_order = "";

        $sql_add_join = "";
        if (!empty($basket['paymentID'])) {
            $sql_add_join .= "
                JOIN s_premium_dispatch_paymentmeans dp
                ON d.id = dp.dispatchID
                AND dp.paymentID={$basket['paymentID']}
            ";
        }
        if (!empty($basket['countryID'])) {
            $sql_add_join .= "
                JOIN s_premium_dispatch_countries dc
                ON d.id = dc.dispatchID
                AND dc.countryID={$basket['countryID']}
            ";
        }

        $sql = "SELECT id, bind_sql FROM s_premium_dispatch WHERE type IN (0) AND bind_sql IS NOT NULL";
        $statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
        $sql_where = "";
        foreach ($statements as $dispatchID => $statement) {
            $sql_where .= "
            AND ( d.id!=$dispatchID OR ($statement))
            ";
        }

        $sql_basket = array();
        foreach ($basket as $key => $value) {
            $sql_basket[] = $this->sDB->qstr($value)." as `$key`";
        }
        $sql_basket = implode(', ',$sql_basket);

        $sql = "
            SELECT d.id, d.name, d.description, d.calculation, d.status_link, d.surcharge_calculation, d.bind_shippingfree, tax_calculation, t.tax as tax_calculation_value, d.shippingfree
            FROM s_premium_dispatch d

            JOIN ( SELECT $sql_basket ) b

            $sql_add_join
            LEFT JOIN (
                SELECT dc.dispatchID
                FROM s_articles_categories_ro ac,
                s_premium_dispatch_categories dc
                WHERE ac.articleID={$basket['articleID']}
                AND dc.categoryID=ac.categoryID
                GROUP BY dc.dispatchID
            ) as dk
            ON d.id = dk.dispatchID

            LEFT JOIN s_core_tax t
            ON t.id=d.tax_calculation

            LEFT JOIN s_user u
            ON u.id=0
            AND u.active=1

            LEFT JOIN s_user_billingaddress ub
            ON ub.userID=u.id

            LEFT JOIN s_user_shippingaddress us
            ON us.userID=u.id

            WHERE d.active = 1
            AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
            AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
            AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
            AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
            AND (bind_laststock=0 OR (bind_laststock=1 AND b.instock) OR (bind_laststock=2 AND b.stockmin))
            AND (bind_shippingfree!=1 OR NOT b.shippingfree)
            AND (d.multishopID IS NULL OR d.multishopID= b.multishopID)
            AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
            AND dk.dispatchID IS NULL
            AND d.type IN (0)
            $sql_where
            ORDER BY $sql_order d.position, d.name
            LIMIT 1
        ";
        $dispatch = $this->sDB->GetRow($sql);
        if (empty($dispatch)) {
            $sql = "
                SELECT
                    d.id, d.name,
                    d.description,
                    d.calculation,
                    d.status_link,
                    d.surcharge_calculation,
                    d.bind_shippingfree,
                    tax_calculation,
                    t.tax as tax_calculation_value
                FROM s_premium_dispatch d
                LEFT JOIN s_core_tax t
                ON t.id=d.tax_calculation
                WHERE d.active=1
                AND d.type=1
                ORDER BY d.position, d.name
                LIMIT 1
            ";
            $dispatch = $this->sDB->GetRow($sql);
        }
        return $dispatch;
    }

    public function sGetPremiumDispatchSurcharge($basket)
    {
        if(empty($basket)) return false;

        $sql = 'SELECT id, bind_sql FROM s_premium_dispatch WHERE type=2 AND bind_sql IS NOT NULL';
        $statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
        $sql_where = '';
        foreach ($statements as $dispatchID => $statement) {
            $sql_where .= "
            AND ( d.id!=$dispatchID OR ($statement))
            ";
        }
        $sql_basket = array();
        foreach ($basket as $key => $value) {
            $sql_basket[] = $this->sSYSTEM->sDB_CONNECTION->qstr($value)." as `$key`";
        }
        $sql_basket = implode(', ',$sql_basket);

                $sql_add_join = "";
        if (!empty($basket['paymentID'])) {
            $sql_add_join .= "
                JOIN s_premium_dispatch_paymentmeans dp
                ON d.id = dp.dispatchID
                AND dp.paymentID={$basket['paymentID']}
            ";
        }
        if (!empty($basket['countryID'])) {
            $sql_add_join .= "
                JOIN s_premium_dispatch_countries dc
                ON d.id = dc.dispatchID
                AND dc.countryID={$basket['countryID']}
            ";
        }

        $sql = "
            SELECT d.id, d.calculation
            FROM s_premium_dispatch d

            JOIN ( SELECT $sql_basket ) b

            $sql_add_join

            LEFT JOIN (
                SELECT dc.dispatchID
                FROM s_articles_categories_ro ac,
                s_premium_dispatch_categories dc
                WHERE ac.articleID={$basket['articleID']}
                AND dc.categoryID=ac.categoryID
                GROUP BY dc.dispatchID
            ) as dk
            ON dk.dispatchID=d.id

            LEFT JOIN s_user u
            ON u.id=b.userID
            AND u.active=1

            LEFT JOIN s_user_billingaddress ub
            ON ub.userID=u.id

            LEFT JOIN s_user_shippingaddress us
            ON us.userID=u.id

            WHERE d.active=1
            AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
            AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
            AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
            AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
            AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))
            AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))
            AND (bind_shippingfree=2 OR NOT b.shippingfree)

            AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)
            AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
            AND dk.dispatchID IS NULL
            AND d.type = 2
            AND (d.shippingfree IS NULL OR d.shippingfree > b.amount)
            $sql_where
            GROUP BY d.id
        ";
        $dispatches = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql);
        $surcharge = 0;
        if(!empty($dispatches))
        foreach ($dispatches as $dispatch) {
            if(empty($dispatch['calculation']))
                $from = round($basket['weight'],3);
            elseif($dispatch['calculation']==1)
                $from = round($basket['amount'],2);
            elseif($dispatch['calculation']==2)
                $from = round($basket['count_article']);
            elseif($dispatch['calculation']==3)
                $from = round($basket['calculation_value_'.$dispatch['id']]);
            else
                continue;
            $sql = "
                SELECT `value` , `factor`
                FROM `s_premium_shippingcosts`
                WHERE `from` <= $from
                AND `dispatchID` = {$dispatch['id']}
                ORDER BY `from` DESC
                LIMIT 1
            ";
            $result = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);
            if(empty($result)) continue;
            $surcharge += $result['value'];
            if(!empty($result['factor']))
                $surcharge +=  $result['factor']/100*$from;
        }
        return $surcharge;
    }

    public function sGetArticlePremiumShippingcosts($article, $payment, $country, $dispatch = null)
    {
        $basket = $this->sGetDispatchBasket($article,$country['id'],$payment['id']);
        if(empty($basket)) return false;
        $dispatch = $this->sGetPremiumDispatch($basket, $dispatch);
        if(empty($dispatch)) return false;

        if ((!empty($dispatch['shippingfree'])&&$dispatch['shippingfree']<=$basket['amount'])
            ||empty($basket['count_article'])
            ||(!empty($basket['shippingfree'])&&empty($dispatch['bind_shippingfree']))
        )
        {
            if(empty($dispatch['surcharge_calculation']))
                return $payment['surcharge'];
            else
                return 0;
        }

        if(empty($dispatch['calculation']))
            $from = round($basket['weight'],3);
        elseif($dispatch['calculation']==1)
            $from = round($basket['amount'],2);
        elseif($dispatch['calculation']==2)
            $from = round($basket['count_article']);
        elseif($dispatch['calculation']==3)
            $from = round($basket['calculation_value_'.$dispatch['id']]);
        else
            return false;

        $sql = "
            SELECT `value` , `factor`
            FROM `s_premium_shippingcosts`
            WHERE `from`<=$from
            AND `dispatchID`={$dispatch['id']}
            ORDER BY `from` DESC
            LIMIT 1
        ";
        $result = $this->sDB->GetRow($sql);

        if(empty($result)) return false;

        $result['shippingcosts'] = $result['value'];
        if(!empty($result['factor']))
            $result['shippingcosts'] +=  $result['factor']/100*$from;
        $result['surcharge'] = $this->sGetPremiumDispatchSurcharge($basket);
        if(!empty($result['surcharge']))
            $result['shippingcosts'] += $result['surcharge'];
        $result['shippingcosts'] *= $this->sCurrency["factor"];
        $result['shippingcosts'] = round($result['shippingcosts'],2);
        if (!empty($payment['surcharge'])&&$dispatch['surcharge_calculation']!=2&&(empty($article['shippingfree'])||empty($dispatch['surcharge_calculation']))) {
            $result['shippingcosts'] += $payment['surcharge'];
        }


        return $result['shippingcosts'];
    }
}
?>
